package Database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
 
public class MySQLConnectionOrders {
 
    private static Connection conn = null;
 
    // Hostname
    private static String dbHost = "instance31105.db.xeround.com";
 
    // Port -- Standard: 3306
    private static String dbPort = "15460";
 
    // Datenbankname
    private static String database = "tomkai";
 
    // Datenbankuser
    private static String dbUser = "anansi";
 
    // Datenbankpasswort
    private static String dbPassword = "amsterdam";
 
    public MySQLConnectionOrders() {
        try {
 
        	
        	      // This will load the MySQL driver, each DB has its own driver
        	      Class.forName("com.mysql.jdbc.Driver");
        	      // Setup the connection with the DB
        	      conn = DriverManager
        	          .getConnection("jdbc:mysql://"+dbHost+":"+dbPort+"/"+database+"?"
        	              + "user="+dbUser+"&password="+dbPassword);
        	      System.out.println("Connected !");
        }   catch (ClassNotFoundException e) {
            System.out.println("Treiber nicht gefunden");
        }   catch (SQLException e) {
            System.out.println("Connect nicht moeglich");
        }
    }
     
    private static Connection getInstance()
    {
        if(conn == null)
            new MySQLConnectionOrders();
        return conn;
    }
 

    
    
    
    public static void druckenamen()
    {
        conn = getInstance();
         
        if(conn != null)
        {
            // Anfrage-Statement erzeugen.
            Statement query;
            try {
                query = conn.createStatement();
 
                // Ergebnistabelle erzeugen und abholen.
                String sql = "SELECT * " + "FROM kunden ORDER BY nummer desc";
                        
                ResultSet result = query.executeQuery(sql);
 
                // Ergebnissätze durchfahren.
                while (result.next()) {
                	int nummer = result.getInt(2);
                    String first_name = result.getString("name"); // Alternativ: result.getString(1);
                    String last_name = result.getString("vorname"); // Alternativ: result.getString(2);
                    String name = nummer+", "+last_name + ", " + first_name;
                    System.out.println(name);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
    
    public static void neuerkunde(String n, String v)
    {
        conn = getInstance();
        String name = n;
        String vorname = v;
         
        if(conn != null)
        {
            // Anfrage-Statement erzeugen.
            Statement query1;
            Statement query2;
            int nummerh=0;
            int nummern;
            try {
                query1 = conn.createStatement();
 
                // Ergebnistabelle erzeugen und abholen.
                String sql1 = "SELECT MAX(nummer) from kunden";
                
                ResultSet result = query1.executeQuery(sql1);
                        
                // Ergebnissätze durchfahren.
                while (result.next()) {
                	nummerh = result.getInt(1);
                }
                if (nummerh != 0)
                {
                nummern = nummerh+1;
                query2 = conn.createStatement();
                String sql2 = "INSERT into kunden values ('"+n+"','"+ nummern+"','"+ v+"')";
                query2.executeUpdate(sql2);
                }
                druckenamen();
                
                

            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
     

     



/**


/**
 * Schreibt die Namensliste in die Konsole
 
public static void printNameList()
{
    conn = getInstance();
     
    if(conn != null)
    {
        // Anfrage-Statement erzeugen.
        Statement query;
        try {
            query = conn.createStatement();

            // Ergebnistabelle erzeugen und abholen.
            String sql = "SELECT first_name, last_name " + "FROM actor "
                    + "ORDER BY last_name";
            ResultSet result = query.executeQuery(sql);

            // Ergebnissätze durchfahren.
            while (result.next()) {
                String first_name = result.getString("first_name"); // Alternativ: result.getString(1);
                String last_name = result.getString("last_name"); // Alternativ: result.getString(2);
                String name = last_name + ", " + first_name;
                System.out.println(name);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}


    /**
     * Fügt einen neuen Datensatz hinzu
     
    public static void insertName(String firstName, String lastName)
    {
        conn = getInstance();
         
        if(conn != null)
        {
            // Anfrage-Statement erzeugen.
            Statement query;
            try {
                query = conn.createStatement();
 
                // Ergebnistabelle erzeugen und abholen.
                String sql = "INSERT INTO actor(first_name, last_name) VALUES('" + firstName + "', '" + lastName +"')";
                query.executeUpdate(sql);
                 
                // Es wird der letzte Datensatz abgefragt
                ResultSet result = query.executeQuery("SELECT actor_id, first_name, last_name FROM actor ORDER BY actor_id desc LIMIT 1");
                 
                // Wenn ein Datensatz gefunden wurde, wird auf diesen Zugegriffen
                if(result.next())
                {
                    System.out.println("(" + result.getInt(1) + ")" + result.getString(2) + " " + result.getString(3));
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
    
    
    /**
     * Aktualisiert den Datensatz mit der übergebenen actorId
     
    public static void updateName(String firstName, String lastName, int actorId)
    {
        conn = getInstance();
         
        if(conn != null)
        {
            // Anfrage-Statement erzeugen.
            Statement query;
            try {
                query = conn.createStatement();
                 
                String querySql = "SELECT actor_id, first_name, last_name FROM actor WHERE actor_id = " + actorId;
                ResultSet result = query.executeQuery(querySql);
                 
                if(result.next())
                {
                    // Vorher
                    System.out.println("VORHER: (" + result.getInt(1) + ")" + result.getString(2) + " " + result.getString(3));
                }
 
                // Ergebnistabelle erzeugen und abholen.
                String updateSql = "UPDATE actor SET first_name = '" + firstName + "', last_name = '" + lastName + "' WHERE actor_id = " + actorId;
                query.executeUpdate(updateSql);
                 
                // Es wird der letzte Datensatz abgefragt
                result = query.executeQuery(querySql);
                 
                if(result.next())
                {
                    System.out.println("NACHHER: (" + result.getInt(1) + ")" + result.getString(2) + " " + result.getString(3));
                }
                 
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
*/